When you need to expose data contained at multiple levels in an XMLType table as individual rows in a relational table (or view), you use the same general approach as for breaking up a single level: Use SQL/XML function XMLTable to define the columns making up the table and map the XML nodes to those columns.

But in this case you apply function XMLTable to each document level that is to be broken up and stored in relational columns. Use this technique of chaining multiple XMLTable calls whenever there is a one-to-many (1:N) relationship between documents in the XMLType table and the rows in the relational table.

You pass one level of XMLType data from one XMLTable call to the next, specifying its column type as XMLType.

When you chain two XMLTable calls, the row pattern of each call should target the deepest node that is a common ancestor to all of the nodes that are referenced in the column patterns of that call.

This is illustrated in Example 4-1.

Each PurchaseOrder element in XMLType table po_binaryxml contains a LineItems element, which in turn contains one or more LineItem elements. Each LineItem element has child elements, such as Description, and an ItemNumber attribute. To make such lower-level data accessible as a relational value, you use XMLTable to project the collection of LineItem elements.

When element PurchaseOrder is decomposed by the first call to XMLTable, its descendant LineItem element is mapped to a column of type XMLType, which contains an XML fragment. That column is then passed to a second call to XMLTable to be broken by it into its various parts as multiple columns of relational values.

The first call to XMLTable uses /PurchaseOrder as the row pattern, because PurchaseOrder is the deepest common ancestor node for the column patterns, Reference and LineItems/LineItem.

The second call to XMLTable uses /LineItem as its row pattern, because that node is the deepest common ancestor node for each of its column patterns (@ItemNumber, Description, Part/@Id, and so on).

The column pattern (LineItems/LineItem) for the column (po.lineitem) that is passed from the first XMLTable call t o the second ends with the repeating element (LineItem) that the second XMLTable call decomposes. That repeating element, written with a leading slash (/), is used as the first element of the row pattern for the second XMLTable call.

The row pattern in each case is thus expressed as an absolute path; that is, it starts with /. It is the starting point for decomposition by XMLTable. Column patterns, on the other hand, never start with a slash (/); they are always relative to the row pattern of the same XMLTable call.

Example 4-1 Chaining XMLTable Calls